﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace OEMS
{
    public class Import
    {
        public static string connectionString;
        public static DataSet excelDataSet;
        public static string sheetName;
        public static int nomToerental;
        public static string klasse;
        public static decimal nomVerm;
        public static string koppelSheet;
        public static string toerentalSheet;
        public static string[] rendementSheet;
        public static int sheetNaamIndex;
        public static string[] workSheetNames = new string[] { };
        public static decimal[,] koppelArray = new decimal[42, 42];
        public static decimal[,] toerArray = new decimal[42, 42];

        /// <summary>
        /// Controleren of de motor niet al in database zit
        /// </summary>
        /// <param name="klasse"></param>
        /// <param name="vermogen"></param>
        /// <param name="toerental"></param>
        /// <returns></returns>
        /// <author>Robin Goris</author>
        public static bool CheckMotorInDatabase(string klasse, decimal vermogen, decimal toerental)
        {
            List<Motor> motorList = MotorDB.GetMotors();
            bool result = false;
            for (int j = 0; j < motorList.Count; j++)
            {
                if (motorList[j].Klasse.Equals(klasse) && motorList[j].NominaalVermogen == vermogen && motorList[j].NominaalToerental == toerental)
                {
                    result = true;
                    break;
                }
            }
            return result;
        }

        /// <summary>
        /// Extentie van excel file controleren
        /// </summary>
        /// <param name="fileName"></param>
        /// <author>Robin Goris</author>
        public static void CheckExtentie(string fileName) {

            //extentie splitsen
            string[] splits = fileName.Split(new char[1] { '.' });

            //Excel 97-2003 file
            if (splits[splits.Length - 1] == "xls")
            {
                OpenExcelFile(false,fileName);
            }
            //Excel 2007 file
            else if (splits[splits.Length - 1] == "xlsx")
            {
                OpenExcelFile(true,fileName);
            }
            
        }


        /// <summary>
        /// Kijken welk extentie de file is (Excel 2007)
        /// </summary>
        /// <param name="isOpenXMLFormat"></param>
        /// <author>Robin Goris</author>
        public static void OpenExcelFile(bool isOpenXMLFormat,string fileNaam)
        {

            //open excel file met OLEDB
            OleDbConnection con;
            
            if (isOpenXMLFormat)
                //2007 file (install driver voor xlsx te kunnen lezen!)
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                    fileNaam + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";
            else
                //97-2003 file
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                fileNaam + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";


            con = new OleDbConnection(connectionString);
            con.Open();

            //Alle sheets in dataTable steken
            DataTable dataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            // sheet namen in array steken
            GetWorkSheetNames(dataTable);

            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
            if (dataTable != null)
                dataTable.Dispose();
        }

        public static String GetWorkSheetNames(DataTable dt)
        {
            /// <summary>
            /// Excel sheetnames ophalen en in Array steken
            /// </summary>
            /// <param name="DataTable">De dataTable van de excel file</param>
            /// <author>codehill.com</author>
            /// <author>Robin Goris : Bug fix & sheetnames met spaties kunnen nu ook gelezen worden</author>
            
            workSheetNames = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                //sheet name in array steken
                //$ + ' teken verwijderen uit sheet naam

                workSheetNames[i] = row["TABLE_NAME"].ToString().Replace("'", "");
                workSheetNames[i] = workSheetNames[i].Replace("$", "");

                i++;
            }
            i -= 1;

            return workSheetNames[i];
        }

        /// <summary>
        /// Haalt de inhoud van een excelsheet naar een dataTable 
        /// </summary>
        /// <param name="worksheetName">de excel sheet naam</param>
        /// <returns>DataTable met de sheet inhoud</returns>
        /// <author>robin Goris</author>
        public static DataTable GetWorksheet(string worksheetName)
        {

            OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
            OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
                "select * from [" + worksheetName + "$]", con);

            con.Open();
            excelDataSet = new DataSet();
            cmd.Fill(excelDataSet);
            sheetName = worksheetName;

            con.Close();

            return excelDataSet.Tables[0];
        }
        /// <summary>
        /// Motor opslaan in dataset
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="sheetNaam"></param>
        /// <author>Robin Goris</author>
        public static void saveMotor(DataSet ds, string sheetNaam)
        {

            Gegevens geg = new Gegevens();

            geg.beschrijving = sheetNaam;

            GegevensDB.AddGegevens(geg);

            Motor motor = new Motor();

            motor.Klasse = klasse;
            motor.Ingebruikname = DateTime.Today;
            motor.NominaalToerental = nomToerental;

            motor.NominaalVermogen = nomVerm;

            List<Gegevens> gegevensList = GegevensDB.GetGegevens();
            int gegAantal = gegevensList.Count;

            motor.GegevensId = gegAantal;

            MotorDB.AddMotorGegevens(motor);

        }

        public static void saveSheet(DataSet ds, string sheetNaam)
        {
            /// <summary>
            /// Waardes uit de excel cellen halen van de sheet 
            /// </summary>
            /// <param name="ds">Dataset van geselecteerde excel sheet</param>
            /// <author>Robin Goris</author>

            for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    Meting met = new Meting();

                    //Als de cel waarde NaN of leeg is deze om zetten naar 0

                    if (ds.Tables[0].Rows[i].ItemArray[j].ToString().Equals("NaN") || (ds.Tables[0].Rows[i].ItemArray[j].ToString().Equals("")))
                    {
                        //controleren welke sheet het is koppel, toerental of rendement..
                        if (sheetName == koppelSheet)
                        {
                            koppelArray[i, j] = 0;

                        }
                        else if (sheetName == toerentalSheet)
                        {
                            toerArray[i, j] = 0;

                        }
                        else
                        {
                            met.Rendement = 0;
                        }
                    }

                    else
                    {
                        if (sheetName == koppelSheet)
                        {
                            koppelArray[i, j] = Convert.ToDecimal(ds.Tables[0].Rows[i].ItemArray[j]);

                        }
                        else if (sheetName == toerentalSheet)
                        {
                            toerArray[i, j] = Convert.ToDecimal(ds.Tables[0].Rows[i].ItemArray[j]);

                        }
                        else
                        {
                            met.Rendement = Convert.ToDecimal(ds.Tables[0].Rows[i].ItemArray[j]);
                        }
                    }

                    if ((sheetName != koppelSheet) && (sheetName != toerentalSheet))
                    {

                        met.Moment = koppelArray[i, j];
                        met.Toerental = toerArray[i, j];

                        met.GegevensId = MotorDB.GetGegegevensId(klasse, nomVerm).GegevensId;

                        MetingDB.AddMotorMeting(met);

                    }
                }
            }
        }
    }
}
